import numpy as np
import pandas as pd
from openpyxl import load_workbook

import common
from common import load_sheet, column_names, merge_rows, chan_expend_names


def chan_shee1(df):
    # select
    df = df[[column_names['chan'], column_names['comm_quat'], 
             column_names['cost'], column_names['dpot_loss'], 
             column_names['sale_amnt'], column_names['chan_loss']]]
    # group
    df = df.groupby([column_names['chan']]).agg('sum').reset_index()
    return df

def chan_shee2(df):
    # select
    df = df[[column_names['chan'], column_names['comm_quat'], 
             column_names['cost'], column_names['dpot_loss'], 
             column_names['sale_amnt'], column_names['chan_loss'],
             column_names['distributor']]]
    # select
    df = df.loc[df[column_names['chan']] == chan_expend_names[0]]
    df = df.drop(columns=[column_names['chan']])
    # group
    df = df.groupby([column_names['distributor']]).agg('sum').reset_index()
    return df

def merge_df(df1, df2):
    # 
    df1 = df1.drop(df1.loc[df1[column_names['chan']] == chan_expend_names[0]].index)
    df = pd.concat([df1, df2.rename(columns={column_names['distributor']: column_names['chan']})], ignore_index=True)
    return df

# 计算销售金额
def calc_sales(df):
    # merge
    df = merge_df(chan_shee1(df), chan_shee2(df)) 
    # merge
    df = merge_rows(df, common.chan_same_names, target = ['chan'])
    # descending sort
    sorted_df = df.sort_values(column_names['sale_amnt'], ascending=False)
    # add summary
    summary = pd.DataFrame({column_names['chan']: column_names['summary'], 
                            column_names['comm_quat']: df[column_names['comm_quat']].sum(), 
                            column_names['cost']:      df[column_names['cost']].sum(), 
                            column_names['dpot_loss']: df[column_names['dpot_loss']].sum(),
                            column_names['sale_amnt']: df[column_names['sale_amnt']].sum(),
                            column_names['chan_loss']: df[column_names['chan_loss']].sum(),
                            },
                            index=[len(df)])
    df = pd.concat([sorted_df, summary])
    # calc profit and profit rate
    df[column_names['profit']] = df[column_names['sale_amnt']] - df[column_names['chan_loss']] - \
        df[column_names['cost']] + df[column_names['dpot_loss']]
    df[column_names['profit_rate']] = df[column_names['profit']] / df[column_names['sale_amnt']] 
    df[column_names['profit_rate']] = df[column_names['profit_rate']].apply(lambda x: f'{x:.2%}')

    return df

def save_excel(xl_path, df, sheet_name):
    workbook = load_workbook(xl_path)
    # 
    if sheet_name in workbook:
        # Delete existing worksheet
        del workbook[sheet_name]
    # 
    worksheet = workbook.create_sheet(sheet_name)
    # 
    worksheet.append(list(df.columns))
    for row in df.values:
        worksheet.append(list(row))
    
    workbook.save(xl_path)

def channel_profit(wb_path, sheet_src_name, sheet_dest_name):

    # 加载sheet
    df = load_sheet(wb_path, sheet_src_name)
    # 
    df = calc_sales(df)
    # 保存至excel
    save_excel(wb_path, df, sheet_dest_name)



if __name__ == '__main__':

    # 
    wb_path = 'data/01-03销售日报.xlsx'
    sheet_src_name = '订单明细'
    sheet_dest_name = '渠道利润率-test'
    channel_profit(wb_path, sheet_src_name, sheet_dest_name)
